﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;

namespace Httlgd.DataCenter.Services.Common
{
    public class SQLHelper
    {
        public static SqlConnection GetConnection()
        {

            string connectionString = ConfigurationManager.ConnectionStrings["MemberManagementConnectionString"].ConnectionString;
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new Exception("Connection String is Empty");
            }
            SqlConnection sqlConn = new SqlConnection(connectionString);
            return sqlConn;
        }

        /// <summary>
        /// Converts the value passed as an object to a string.
        /// </summary>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
        public static string GetStringValue(object fieldValue)
        {
            if (fieldValue != DBNull.Value)
            {
                return fieldValue.ToString();
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        ///  Converts the value passed as an object to an int.
        /// </summary>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
        public static int GetIntValue(object fieldValue)
        {
            if (fieldValue != DBNull.Value)
            {
                return Convert.ToInt32(fieldValue.ToString());
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        ///  Converts the value passed as an object to a decimal.
        /// </summary>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
        public static decimal GetDecimalValue(object fieldValue)
        {
            if (fieldValue != DBNull.Value)
            {
                return Convert.ToDecimal(fieldValue.ToString());
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        ///  Converts the value passed as an object to a  nullable int.
        /// </summary>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
        public static int? GetNullableIntValue(object fieldValue)
        {
            if (fieldValue != DBNull.Value)
            {
                return Convert.ToInt32(fieldValue.ToString());
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        ///  Converts the value passed as an object to a bool.
        /// </summary>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
        public static bool GetBoolValue(object fieldValue)
        {
            if (fieldValue != DBNull.Value)
            {
                return Convert.ToBoolean(fieldValue.ToString());
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        ///  Converts the value passed as an object to a datetime.
        /// </summary>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
        public static DateTime GetDateTimeValue(object fieldValue)
        {
            if (fieldValue != DBNull.Value)
            {
                return Convert.ToDateTime(fieldValue.ToString());
            }
            else
            {
                return DateTime.MinValue;
            }
        }

        /// <summary>
        ///  Converts the value passed as an object to a nullable datetime.
        /// </summary>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
        public static DateTime? GetNullableDateTimeValue(object fieldValue)
        {
            if (fieldValue != DBNull.Value && fieldValue != null)
            {
                return Convert.ToDateTime(fieldValue.ToString());
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Checks to see if a column exists in the datareader.
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public static bool ColumnExistsInReader(SqlDataReader reader, string columnName)
        {
            reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '" + columnName + "'";
            return (reader.GetSchemaTable().DefaultView.Count > 0);
        }

        /// <summary>
        /// This method returns the a datetime value with the datepart as the sqlmindate and time part
        /// from the dateValue passed.
        /// </summary>
        /// <param name="dateValue"></param>
        /// <returns></returns>
        public static DateTime GetTimeValue(DateTime dateValue)
        {
            return DateTime.Parse(DateTime.Parse(System.Data.SqlTypes.SqlDateTime.MinValue.ToString()).ToShortDateString() + " " + dateValue.ToShortTimeString());
        }

        /// <summary>
        /// This method returns the a smalldatetime value with the datepart as the sqlmindate and time part
        /// from the dateValue passed.
        /// </summary>
        /// <param name="dateValue"></param>
        /// <returns></returns>
        public static DateTime GetSmallTimeValue(DateTime dateValue)
        {
            DateTime sqlSmallDateTimeMinValue = new DateTime(1900, 1, 1);
            return DateTime.Parse(DateTime.Parse(sqlSmallDateTimeMinValue.ToString()).ToShortDateString() + " " + dateValue.ToShortTimeString());
        }
    }
}
